﻿using System.Collections;
using umbraco.cms.businesslogic.member;

namespace Designit.Umb.Newsletter.Bll
{
    public class ConfirmUnsubscribe
    {
        public static Member Confirm(string guid)
        {
            int memberid =
                umbraco.BusinessLogic.Application.SqlHelper.ExecuteScalar<int>("select top 1 memberId from newsLetterUnsubscribeMember where guid=@guid",
                                                                               umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@guid", guid));
            string mail =
                umbraco.BusinessLogic.Application.SqlHelper.ExecuteScalar<string>("select top 1 Email from cmsMember where nodeId=@memberid",
                                                                                  umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@memberid", memberid));
            Member member = Member.GetMemberFromEmail(mail);
            using (var reader =
                umbraco.BusinessLogic.Application.SqlHelper.ExecuteReader("select * from newsLetterUnsubscribeMember where guid=@guid",
                                                                          umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@guid", guid)))
            {
                while (reader.Read())
                {
                    member.RemoveGroup(reader.GetInt("groupId"));
                }
            }
            RemoveFromActiveCampaign(memberid);
            umbraco.BusinessLogic.Application.SqlHelper.ExecuteNonQuery("delete from newsLetterUnsubscribeMember where guid=@guid",
                                                                        umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@guid", guid));
            return member;
        }
        private static void RemoveFromActiveCampaign(int memberid)
        {
            ArrayList member2Group = new ArrayList();
            //retrieve members active subscribtions

            using (var reader =
                umbraco.BusinessLogic.Application.SqlHelper.ExecuteReader("select * from cmsMember2MemberGroup where Member=@Member",
                                                                          umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@Member", memberid)))
            {
                //store members active subscribtion id's in arraylist
                while (reader.Read())
                {
                    member2Group.Add(reader.GetInt("MemberGroup"));
                }
            }

            ArrayList campaignId = new ArrayList();

            //retrieve drafted campaigns
            using (var reader =
                umbraco.BusinessLogic.Application.SqlHelper.ExecuteReader("select id from newsLetterCampaignNewsletter where campstatus='draft'; "))
            {
                //store drafted campaign's id
                while (reader.Read())
                {
                    campaignId.Add(reader.GetInt("id"));
                }
            }

            //add the drafted campaign's id which member is not subscribing to
            ArrayList unsubCamp = new ArrayList();
            foreach (int id in campaignId)
            {
                bool found = false;
                using (var readerMemberIds =
                    umbraco.BusinessLogic.Application.SqlHelper.ExecuteReader("select groupId from newsLetterCampaign2Group where campaignId=@campaignId",
                                                                              umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@campaignId", id)))
                {
                    while (readerMemberIds.Read() && !found)
                    {
                        if (member2Group.Contains(readerMemberIds.GetInt("groupId")))
                        {
                            found = true;
                        }
                    }
                }
                if (!found)
                {
                    unsubCamp.Add(id);
                }
            }

            //remove campaign subscribtion to no longer active subscribtions
            foreach (int id in unsubCamp)
            {
                umbraco.BusinessLogic.Application.SqlHelper.ExecuteNonQuery(
                    "delete from newsLetterCampaign2Member where memberId=@memberId AND campaignId=@campaignID",
                    umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@memberId", memberid),
                    umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@campaignId", id));
            }

        }
    }
}
